package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.CcDao;
import com.mp.approvalreq.entity.Cc;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by liuqi on 2018/7/3.
 */
public class CcDaoImpl extends AbstractDao implements CcDao {

    private static final Logger logger = LoggerFactory.getLogger(CcDaoImpl.class);

    private Connection conn;

    public CcDaoImpl(Connection conn) {
        this.conn = conn;
    }

    private PreparedStatement getPrepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select Fid, Frequest_id, Fwho_filed, Fwho_filed_ciid, Ffiled_time,"
                + " Fcompany_id, Fis_temp, Fcrt_by, Ffiled_status, Fis_read"
                + " from request_filed";
        return String.format("%s %s", query, condition);
    }

    @Override
    Cc getObject(ResultSet rs) throws SQLException {
        Cc cc = new Cc();
        cc.setId(rs.getInt(1));
        cc.setRequestId(rs.getInt(2));
        cc.setCcId(rs.getInt(3));
        cc.setCcCiid(rs.getInt(4));
        cc.setCcTime(DateTimeHelper.format(rs.getTimestamp(5), DateTimeHelper.DEFAULT_PATTERN));

        cc.setCompanyId(rs.getInt(6));
        cc.setIsTemp(rs.getInt(7));
        cc.setCreatorId(rs.getInt(8));
        cc.setFiledStatus(rs.getInt(9));
        cc.setIsRead(rs.getInt(10));
        return cc;
    }


    @Override
    public void letCcCouldFile(Integer requestId, Integer companyId) throws SQLException {
        String sql = " update approval.request_filed set Ffiled_status = 1, Fis_read = 0 " +
                " where Frequest_id = ? and Fcompany_id  = ? and Fstatus = 0 and Ffiled_status = 0 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }


    @Override
    public void letCcCouldNotFile(Integer requestId, Integer companyId) throws SQLException {
        String sql = " update approval.request_filed set Ffiled_status = 0, Fis_read = 0 " +
                " where Frequest_id = ? and Fcompany_id = ? and Fstatus = 0 ";

        try (PreparedStatement pstm = getPrepareStatement(sql)) {
            pstm.setInt(1, requestId);
            pstm.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            pstm.executeUpdate();
        }
    }


    @Override
    public void deleteCcByRequestId(Integer requestId, Integer companyId) throws SQLException {

        String sql = "update request_filed set "
                + " Ffiled_status = 0, Fstatus = 1, Fdisable_time = now() "
                + " where Frequest_id = ? and Fcompany_id = ? ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }


    @Override
    public void removeUnFileAndUnReadRequests(Integer companyInfoId) throws SQLException {

        String sql = " update approval.request_filed set Fis_read = 1 " +
                " where Fwho_filed_ciid = ? and Ffiled_status = 1 and Fis_read = 0 and Fis_read = 0 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            ps.executeUpdate();
        }
    }


    @Override
    public void deleteRedPoint(Integer requestId, Integer companyInfoId) throws SQLException {

        String sql = " update approval.request_filed set Fis_read = 1 " +
                " where Frequest_id = ? and Fwho_filed_ciid = ? and Ffiled_status = 1 and Fis_read = 0 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyInfoId));

            ps.executeUpdate();
        }
    }

    @Override
    public void deleteRedPoint(Integer requestId) throws SQLException {

        String sql = " update approval.request_filed set Fis_read = 1 " +
                " where Frequest_id = ? and Ffiled_status = 1 and Fis_read = 0 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            ps.executeUpdate();
        }
    }

    @Override
    public void deleteUserCcStatus(List<Integer> requestIds, Integer companyInfoId) throws SQLException {
        String sql = " update approval.request_filed set Ffiled_status = 0, Fstatus = 1, Fdisable_time = now()" +
                " where Frequest_id = ? and Fwho_filed_ciid = ? ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            for (Integer requestId : requestIds) {
                ps.setInt(1, requestId);
                ps.setInt(2, companyInfoId);
                logger.info(SQLLogger.logSQL(sql, requestId, companyInfoId));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }


    @Override
    public List<Integer> getNeedToFileRequestIds(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> requestIds = new LinkedList<>();

        String sql = " select Frequest_id from approval.request_filed " +
                " where Fcompany_id = ? and Fwho_filed_ciid = ? and Ffiled_status = 1 and Fstatus = 0 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    requestIds.add(rs.getInt(1));
                }
            }
        }
        return requestIds;
    }


    @Override
    public List<Integer> getMyArchivedHasRedPointRequestIds(String redPointRequestIds, Integer companyInfoId) throws SQLException {
        List<Integer> requestIds = new LinkedList<>();

        String sql = " select Frequest_id from approval.request_filed where Fwho_filed_ciid = ? " +
                " and Ffiled_status = 2 and Fstatus = 0 and Frequest_id in(" + redPointRequestIds + ")";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyInfoId);

            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    requestIds.add(rs.getInt(1));
                }
            }
        }

        return requestIds;
    }

    @Override
    public List<Integer> selectNeedToFileRequestIds(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> list = new LinkedList<>();

        String sql = "select Frequest_id from request_filed where Fcompany_id = ? and Fwho_filed_ciid = ?"
                + " and Ffiled_status = 1 and Fstatus = 0 order by Frequest_id desc";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectMyFiledRequestIds(Integer companyId, Integer companyInfoId, String orderBy) throws SQLException {
        List<Integer> list = new ArrayList<>();

        String sql = "select filed.Frequest_id"
                + " from request_filed filed"
                + " left join request_flow f on filed.Frequest_id = f.Frequest_id"
                + " where filed.Fcompany_id = ? and filed.Fwho_filed_ciid = ? and filed.Ffiled_status = 2"
                + " and filed.Fstatus = 0 and f.Ffinished in(1,2)";

        // 排序
        if (StringUtils.isEmpty(orderBy) || "0".equals(orderBy) || "1".equals(orderBy)) {
            sql += " order by f.Frequest_id desc";
        } else if ("2".equals(orderBy)) {
            sql += " order by f.Flatest_approved_time desc";
        } else if ("3".equals(orderBy)) {
            sql += " order by f.Ffinally_cc_time desc";
        }

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectCcUnReadRequestIds(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = " select Frequest_id from request_filed where Fcompany_id = ? and Fwho_filed_ciid = ?"
                + " and Ffiled_status = 1 and Fstatus = 0 and Fis_read = 0";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public Map<Integer, List<Integer>> selectArchivedCompanyInfoMap(String requestIds) throws SQLException {
        Map<Integer, List<Integer>> map = new HashMap<>();
        if (StringUtils.isBlank(requestIds)) {
            return map;
        }
        String sql = "select Frequest_id, Fwho_filed_ciid"
                + " from approval.request_filed "
                + " where Frequest_id in(" + requestIds + ") and Fstatus = 0 and Ffiled_status = 2  ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    if (!map.containsKey(rs.getInt(1))) {
                        map.put(rs.getInt(1), new ArrayList<>());
                    }
                    map.get(rs.getInt(1)).add(rs.getInt(2));
                }
            }
        }
        return map;
    }

    @Override
    public List<Integer> selectUserCcRequestIds(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select Frequest_id"
                + " from request_filed"
                + " where Fcompany_id = ? and Fwho_filed_ciid = ?";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void insertBatchCc(List<Cc> ccList) throws SQLException {
        String sql = "insert into request_filed set"
                + " Frequest_id = ?, Fwho_filed = ?, Fwho_filed_ciid = ?, Fcompany_id = ?, Fis_temp = ?,"
                + " Fcrt_by = ?, Ffiled_status = ?, Fcreate_time = now()";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            for (Cc cc : ccList) {
                ps.setInt(1, cc.getRequestId());
                ps.setInt(2, cc.getCcId());
                ps.setInt(3, cc.getCcCiid());
                ps.setInt(4, cc.getCompanyId());
                ps.setInt(5, cc.getIsTemp());

                if (Utils.isNotNull(cc.getCreatorId())) {
                    ps.setInt(6, cc.getCreatorId());
                } else {
                    ps.setObject(6, cc.getCreatorId());
                }
                ps.setInt(7, cc.getFiledStatus());
                logger.info(SQLLogger.logSQL(sql, cc.getRequestId(), cc.getCcId(), cc.getCcCiid(), cc.getCompanyId(), cc.getIsTemp(),
                        cc.getCreatorId(), cc.getFiledStatus()));
                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Cc> selectCcListByRequestId(Integer requestId) throws SQLException {
        List<Cc> list = new ArrayList<>();
        String sql = getQuerySql("where Frequest_id = ? and Fstatus = 0");

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public void deleteCc(Cc cc) throws SQLException {
        String sql = "update request_filed set Fstatus = 1, Fdisable_time = now() where Fid = ? and Fcompany_id = ? limit 1";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, cc.getId());
            ps.setInt(2, cc.getCompanyId());
            logger.info(SQLLogger.logSQL(sql, cc.getId(), cc.getCompanyId()));

            ps.executeUpdate();
        }
    }

    @Override
    public Map<Integer, List<Cc>> selectBatchCc(String requestIds) throws SQLException {
        Map<Integer, List<Cc>> map = new HashMap<>();
        if (StringUtils.isEmpty(requestIds)) {
            return map;
        }
        String sql = getQuerySql("where Frequest_id in(" + requestIds + ") and Fstatus = 0");

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Cc cc = getObject(rs);
                    if (!map.containsKey(cc.getRequestId())) {
                        map.put(cc.getRequestId(), new ArrayList<>());
                    }
                    map.get(cc.getRequestId()).add(cc);
                }
            }
        }
        return map;
    }

    @Override
    public void updateCcFiledStatus(Cc cc) throws SQLException {
        String sql = "update request_filed set Ffiled_status = ?, Ffiled_time = ?, Fis_read = 1 where Fid = ? and Fcompany_id = ? limit 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, cc.getFiledStatus());
            ps.setString(2, cc.getCcTime());

            ps.setInt(3, cc.getId());
            ps.setInt(4, cc.getCompanyId());
            logger.info(SQLLogger.logSQL(sql, cc.getFiledStatus(), cc.getCcTime(), cc.getId(), cc.getCompanyId()));

            ps.executeUpdate();
        }
    }

    @Override
    public void updateBatchCcFiledStatus(List<Cc> ccList) throws SQLException {
        String sql = "update request_filed set Ffiled_status = ?, Ffiled_time = ?, Fis_read = 1 where Fid = ? and Fcompany_id = ? limit 1 ";

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            for (Cc cc : ccList) {
                ps.setInt(1, cc.getFiledStatus());
                ps.setString(2, cc.getCcTime());

                ps.setInt(3, cc.getId());
                ps.setInt(4, cc.getCompanyId());
                logger.info(SQLLogger.logSQL(sql, cc.getFiledStatus(), cc.getCcTime(), cc.getId(), cc.getCompanyId()));

                ps.addBatch();
            }

            ps.executeBatch();
        }
    }

    @Override
    public List<Cc> selectCcRedPointInfo(Integer requestId, Integer companyInfoId) throws SQLException {
        List<Cc> list = new ArrayList<>();
        String sql = getQuerySql("where Frequest_id = ? and Fwho_filed_ciid = ? and Ffiled_status = 1 and Fis_read = 0");

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Cc> selectCcRedPointInfo(Integer requestId) throws SQLException {
        List<Cc> list = new ArrayList<>();
        String sql = getQuerySql("where Frequest_id = ? and Ffiled_status = 1 and Fis_read = 0");

        try (PreparedStatement ps = getPrepareStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }


}